{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Aggregation and Group Operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "from __future__ import division\n",
    "from numpy.random import randn\n",
    "import numpy as np\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "np.random.seed(12345)\n",
    "plt.rc('figure', figsize=(10, 6))\n",
    "from pandas import Series, DataFrame\n",
    "import pandas as pd\n",
    "np.set_printoptions(precision=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.notebook_repr_html = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GroupBy mechanics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  key1 key2     data1     data2\n",
       "0    a  one -0.204708  1.393406\n",
       "1    a  two  0.478943  0.092908\n",
       "2    b  one -0.519439  0.281746\n",
       "3    b  two -0.555730  0.769023\n",
       "4    a  one  1.965781  1.246435"
      ]
     },
     "metadata": {},
     "execution_count": 78
    }
   ],
   "source": [
    "df = DataFrame({'key1' : ['a', 'a', 'b', 'b', 'a'],\n",
    "                'key2' : ['one', 'two', 'one', 'two', 'one'],\n",
    "                'data1' : np.random.randn(5),\n",
    "                'data2' : np.random.randn(5)})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.SeriesGroupBy object at 0x000000000C2BDA20>"
      ]
     },
     "metadata": {},
     "execution_count": 79
    }
   ],
   "source": [
    "grouped = df['data1'].groupby(df['key1'])\n",
    "grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "key1\n",
       "a    0.746672\n",
       "b   -0.537585\n",
       "Name: data1, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 80
    }
   ],
   "source": [
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "key1  key2\n",
       "a     one     0.880536\n",
       "      two     0.478943\n",
       "b     one    -0.519439\n",
       "      two    -0.555730\n",
       "Name: data1, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 81
    }
   ],
   "source": [
    "means = df['data1'].groupby([df['key1'], df['key2']]).mean()\n",
    "means"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "key2       one       two\n",
       "key1                    \n",
       "a     0.880536  0.478943\n",
       "b    -0.519439 -0.555730"
      ]
     },
     "metadata": {},
     "execution_count": 82
    }
   ],
   "source": [
    "means.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "California  2005    0.478943\n",
       "            2006   -0.519439\n",
       "Ohio        2005   -0.380219\n",
       "            2006    1.965781\n",
       "Name: data1, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 83
    }
   ],
   "source": [
    "states = np.array(['Ohio', 'California', 'California', 'Ohio', 'Ohio'])\n",
    "years = np.array([2005, 2005, 2006, 2005, 2006])\n",
    "df['data1'].groupby([states, years]).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "         data1     data2\n",
       "key1                    \n",
       "a     0.746672  0.910916\n",
       "b    -0.537585  0.525384"
      ]
     },
     "metadata": {},
     "execution_count": 84
    }
   ],
   "source": [
    "df.groupby('key1').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              data1     data2\n",
       "key1 key2                    \n",
       "a    one   0.880536  1.319920\n",
       "     two   0.478943  0.092908\n",
       "b    one  -0.519439  0.281746\n",
       "     two  -0.555730  0.769023"
      ]
     },
     "metadata": {},
     "execution_count": 85
    }
   ],
   "source": [
    "df.groupby(['key1', 'key2']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "key1  key2\n",
       "a     one     2\n",
       "      two     1\n",
       "b     one     1\n",
       "      two     1\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 86
    }
   ],
   "source": [
    "df.groupby(['key1', 'key2']).size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Iterating over groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "a\n  key1 key2     data1     data2\n0    a  one -0.204708  1.393406\n1    a  two  0.478943  0.092908\n4    a  one  1.965781  1.246435\nb\n  key1 key2     data1     data2\n2    b  one -0.519439  0.281746\n3    b  two -0.555730  0.769023\n"
     ]
    }
   ],
   "source": [
    "for name, group in df.groupby('key1'):\n",
    "    print(name)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "('a', 'one')\n  key1 key2     data1     data2\n0    a  one -0.204708  1.393406\n4    a  one  1.965781  1.246435\n('a', 'two')\n  key1 key2     data1     data2\n1    a  two  0.478943  0.092908\n('b', 'one')\n  key1 key2     data1     data2\n2    b  one -0.519439  0.281746\n('b', 'two')\n  key1 key2    data1     data2\n3    b  two -0.55573  0.769023\n"
     ]
    }
   ],
   "source": [
    "for (k1, k2), group in df.groupby(['key1', 'key2']):\n",
    "    print((k1, k2))\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  key1 key2     data1     data2\n",
       "2    b  one -0.519439  0.281746\n",
       "3    b  two -0.555730  0.769023"
      ]
     },
     "metadata": {},
     "execution_count": 89
    }
   ],
   "source": [
    "pieces = dict(list(df.groupby('key1')))\n",
    "pieces['b']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('a',   key1 key2     data1     data2\n",
       "  0    a  one -0.204708  1.393406\n",
       "  1    a  two  0.478943  0.092908\n",
       "  4    a  one  1.965781  1.246435), ('b',   key1 key2     data1     data2\n",
       "  2    b  one -0.519439  0.281746\n",
       "  3    b  two -0.555730  0.769023)]"
      ]
     },
     "metadata": {},
     "execution_count": 90
    }
   ],
   "source": [
    "list(df.groupby('key1'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "key1      object\n",
       "key2      object\n",
       "data1    float64\n",
       "data2    float64\n",
       "dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 91
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "{dtype('float64'):       data1     data2\n",
       " 0 -0.204708  1.393406\n",
       " 1  0.478943  0.092908\n",
       " 2 -0.519439  0.281746\n",
       " 3 -0.555730  0.769023\n",
       " 4  1.965781  1.246435, dtype('O'):   key1 key2\n",
       " 0    a  one\n",
       " 1    a  two\n",
       " 2    b  one\n",
       " 3    b  two\n",
       " 4    a  one}"
      ]
     },
     "metadata": {},
     "execution_count": 92
    }
   ],
   "source": [
    "grouped = df.groupby(df.dtypes, axis=1)\n",
    "dict(list(grouped))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting a column or subset of columns"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "df.groupby('key1')['data1']\n",
    "df.groupby('key1')[['data2']]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "df['data1'].groupby(df['key1'])\n",
    "df[['data2']].groupby(df['key1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              data2\n",
       "key1 key2          \n",
       "a    one   1.319920\n",
       "     two   0.092908\n",
       "b    one   0.281746\n",
       "     two   0.769023"
      ]
     },
     "metadata": {},
     "execution_count": 93
    }
   ],
   "source": [
    "df.groupby(['key1', 'key2'])[['data2']].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.SeriesGroupBy object at 0x000000000D2BAB70>"
      ]
     },
     "metadata": {},
     "execution_count": 94
    }
   ],
   "source": [
    "s_grouped = df.groupby(['key1', 'key2'])['data2']\n",
    "s_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "key1  key2\n",
       "a     one     1.319920\n",
       "      two     0.092908\n",
       "b     one     0.281746\n",
       "      two     0.769023\n",
       "Name: data2, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 95
    }
   ],
   "source": [
    "s_grouped.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping with dicts and Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "ipykernel_launcher:4: DeprecationWarning: \n.ix is deprecated. Please use\n.loc for label based indexing or\n.iloc for positional indexing\n\nSee the documentation here:\nhttp://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               a         b         c         d         e\n",
       "Joe     1.007189 -1.296221  0.274992  0.228913  1.352917\n",
       "Steve   0.886429 -2.001637 -0.371843  1.669025 -0.438570\n",
       "Wes    -0.539741       NaN       NaN -1.021228 -0.577087\n",
       "Jim     0.124121  0.302614  0.523772  0.000940  1.343810\n",
       "Travis -0.713544 -0.831154 -2.370232 -1.860761 -0.860757"
      ]
     },
     "metadata": {},
     "execution_count": 96
    }
   ],
   "source": [
    "people = DataFrame(np.random.randn(5, 5),\n",
    "                   columns=['a', 'b', 'c', 'd', 'e'],\n",
    "                   index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis'])\n",
    "people.ix[2:3, ['b', 'c']] = np.nan # Add a few NA values\n",
    "people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapping = {'a': 'red', 'b': 'red', 'c': 'blue',\n",
    "           'd': 'blue', 'e': 'red', 'f' : 'orange'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "            blue       red\n",
       "Joe     0.503905  1.063885\n",
       "Steve   1.297183 -1.553778\n",
       "Wes    -1.021228 -1.116829\n",
       "Jim     0.524712  1.770545\n",
       "Travis -4.230992 -2.405455"
      ]
     },
     "metadata": {},
     "execution_count": 98
    }
   ],
   "source": [
    "by_column = people.groupby(mapping, axis=1)\n",
    "by_column.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "a       red\n",
       "b       red\n",
       "c      blue\n",
       "d      blue\n",
       "e       red\n",
       "f    orange\n",
       "dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 99
    }
   ],
   "source": [
    "map_series = Series(mapping)\n",
    "map_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "        blue  red\n",
       "Joe        2    3\n",
       "Steve      2    3\n",
       "Wes        1    2\n",
       "Jim        2    3\n",
       "Travis     2    3"
      ]
     },
     "metadata": {},
     "execution_count": 100
    }
   ],
   "source": [
    "people.groupby(map_series, axis=1).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping with functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "          a         b         c         d         e\n",
       "3  0.591569 -0.993608  0.798764 -0.791374  2.119639\n",
       "5  0.886429 -2.001637 -0.371843  1.669025 -0.438570\n",
       "6 -0.713544 -0.831154 -2.370232 -1.860761 -0.860757"
      ]
     },
     "metadata": {},
     "execution_count": 101
    }
   ],
   "source": [
    "people.groupby(len).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              a         b         c         d         e\n",
       "3 one -0.539741 -1.296221  0.274992 -1.021228 -0.577087\n",
       "  two  0.124121  0.302614  0.523772  0.000940  1.343810\n",
       "5 one  0.886429 -2.001637 -0.371843  1.669025 -0.438570\n",
       "6 two -0.713544 -0.831154 -2.370232 -1.860761 -0.860757"
      ]
     },
     "metadata": {},
     "execution_count": 102
    }
   ],
   "source": [
    "key_list = ['one', 'one', 'one', 'two', 'two']\n",
    "people.groupby([len, key_list]).min()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping by index levels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "cty          US                            JP          \n",
       "tenor         1         3         5         1         3\n",
       "0      0.560145 -1.265934  0.119827 -1.063512  0.332883\n",
       "1     -2.359419 -0.199543 -1.541996 -0.970736 -1.307030\n",
       "2      0.286350  0.377984 -0.753887  0.331286  1.349742\n",
       "3      0.069877  0.246674 -0.011862  1.004812  1.327195"
      ]
     },
     "metadata": {},
     "execution_count": 103
    }
   ],
   "source": [
    "columns = pd.MultiIndex.from_arrays([['US', 'US', 'US', 'JP', 'JP'],\n",
    "                                    [1, 3, 5, 1, 3]], names=['cty', 'tenor'])\n",
    "hier_df = DataFrame(np.random.randn(4, 5), columns=columns)\n",
    "hier_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "cty  JP  US\n",
       "0     2   3\n",
       "1     2   3\n",
       "2     2   3\n",
       "3     2   3"
      ]
     },
     "metadata": {},
     "execution_count": 104
    }
   ],
   "source": [
    "hier_df.groupby(level='cty', axis=1).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  key1 key2     data1     data2\n",
       "0    a  one -0.204708  1.393406\n",
       "1    a  two  0.478943  0.092908\n",
       "2    b  one -0.519439  0.281746\n",
       "3    b  two -0.555730  0.769023\n",
       "4    a  one  1.965781  1.246435"
      ]
     },
     "metadata": {},
     "execution_count": 105
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "key1\n",
       "a    1.668413\n",
       "b   -0.523068\n",
       "Name: data1, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 106
    }
   ],
   "source": [
    "grouped = df.groupby('key1')\n",
    "grouped['data1'].quantile(0.9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "         data1     data2\n",
       "key1                    \n",
       "a     2.170488  1.300498\n",
       "b     0.036292  0.487276"
      ]
     },
     "metadata": {},
     "execution_count": 107
    }
   ],
   "source": [
    "def peak_to_peak(arr):\n",
    "    return arr.max() - arr.min()\n",
    "grouped.agg(peak_to_peak)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     data1                                                              \\\n",
       "     count      mean       std       min       25%       50%       75%   \n",
       "key1                                                                     \n",
       "a      3.0  0.746672  1.109736 -0.204708  0.137118  0.478943  1.222362   \n",
       "b      2.0 -0.537585  0.025662 -0.555730 -0.546657 -0.537585 -0.528512   \n",
       "\n",
       "               data2                                                    \\\n",
       "           max count      mean       std       min       25%       50%   \n",
       "key1                                                                     \n",
       "a     1.965781   3.0  0.910916  0.712217  0.092908  0.669671  1.246435   \n",
       "b    -0.519439   2.0  0.525384  0.344556  0.281746  0.403565  0.525384   \n",
       "\n",
       "                          \n",
       "           75%       max  \n",
       "key1                      \n",
       "a     1.319920  1.393406  \n",
       "b     0.647203  0.769023  "
      ]
     },
     "metadata": {},
     "execution_count": 108
    }
   ],
   "source": [
    "grouped.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   total_bill   tip     sex smoker  day    time  size   tip_pct\n",
       "0       16.99  1.01  Female     No  Sun  Dinner     2  0.059447\n",
       "1       10.34  1.66    Male     No  Sun  Dinner     3  0.160542\n",
       "2       21.01  3.50    Male     No  Sun  Dinner     3  0.166587\n",
       "3       23.68  3.31    Male     No  Sun  Dinner     2  0.139780\n",
       "4       24.59  3.61  Female     No  Sun  Dinner     4  0.146808\n",
       "5       25.29  4.71    Male     No  Sun  Dinner     4  0.186240"
      ]
     },
     "metadata": {},
     "execution_count": 109
    }
   ],
   "source": [
    "tips = pd.read_csv('ch08/tips.csv')\n",
    "# Add tip percentage of total bill\n",
    "tips['tip_pct'] = tips['tip'] / tips['total_bill']\n",
    "tips[:6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Column-wise and multiple function application"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped = tips.groupby(['sex', 'smoker'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "sex     smoker\n",
       "Female  No        0.156921\n",
       "        Yes       0.182150\n",
       "Male    No        0.160669\n",
       "        Yes       0.152771\n",
       "Name: tip_pct, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 111
    }
   ],
   "source": [
    "grouped_pct = grouped['tip_pct']\n",
    "grouped_pct.agg('mean')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                   mean       std  peak_to_peak\n",
       "sex    smoker                                  \n",
       "Female No      0.156921  0.036421      0.195876\n",
       "       Yes     0.182150  0.071595      0.360233\n",
       "Male   No      0.160669  0.041849      0.220186\n",
       "       Yes     0.152771  0.090588      0.674707"
      ]
     },
     "metadata": {},
     "execution_count": 112
    }
   ],
   "source": [
    "grouped_pct.agg(['mean', 'std', peak_to_peak])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                    foo       bar\n",
       "sex    smoker                    \n",
       "Female No      0.156921  0.036421\n",
       "       Yes     0.182150  0.071595\n",
       "Male   No      0.160669  0.041849\n",
       "       Yes     0.152771  0.090588"
      ]
     },
     "metadata": {},
     "execution_count": 113
    }
   ],
   "source": [
    "grouped_pct.agg([('foo', 'mean'), ('bar', np.std)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              tip_pct                     total_bill                  \n",
       "                count      mean       max      count       mean    max\n",
       "sex    smoker                                                         \n",
       "Female No          54  0.156921  0.252672         54  18.105185  35.83\n",
       "       Yes         33  0.182150  0.416667         33  17.977879  44.30\n",
       "Male   No          97  0.160669  0.291990         97  19.791237  48.33\n",
       "       Yes         60  0.152771  0.710345         60  22.284500  50.81"
      ]
     },
     "metadata": {},
     "execution_count": 114
    }
   ],
   "source": [
    "functions = ['count', 'mean', 'max']\n",
    "result = grouped['tip_pct', 'total_bill'].agg(functions)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               count      mean       max\n",
       "sex    smoker                           \n",
       "Female No         54  0.156921  0.252672\n",
       "       Yes        33  0.182150  0.416667\n",
       "Male   No         97  0.160669  0.291990\n",
       "       Yes        60  0.152771  0.710345"
      ]
     },
     "metadata": {},
     "execution_count": 115
    }
   ],
   "source": [
    "result['tip_pct']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                   tip_pct              total_bill           \n",
       "              Durchschnitt Abweichung Durchschnitt Abweichung\n",
       "sex    smoker                                                \n",
       "Female No         0.156921   0.001327    18.105185  53.092422\n",
       "       Yes        0.182150   0.005126    17.977879  84.451517\n",
       "Male   No         0.160669   0.001751    19.791237  76.152961\n",
       "       Yes        0.152771   0.008206    22.284500  98.244673"
      ]
     },
     "metadata": {},
     "execution_count": 116
    }
   ],
   "source": [
    "ftuples = [('Durchschnitt', 'mean'), ('Abweichung', np.var)]\n",
    "grouped['tip_pct', 'total_bill'].agg(ftuples)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                tip  size\n",
       "sex    smoker            \n",
       "Female No       5.2   140\n",
       "       Yes      6.5    74\n",
       "Male   No       9.0   263\n",
       "       Yes     10.0   150"
      ]
     },
     "metadata": {},
     "execution_count": 117
    }
   ],
   "source": [
    "grouped.agg({'tip' : np.max, 'size' : 'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                tip_pct                               size\n",
       "                    min       max      mean       std  sum\n",
       "sex    smoker                                             \n",
       "Female No      0.056797  0.252672  0.156921  0.036421  140\n",
       "       Yes     0.056433  0.416667  0.182150  0.071595   74\n",
       "Male   No      0.071804  0.291990  0.160669  0.041849  263\n",
       "       Yes     0.035638  0.710345  0.152771  0.090588  150"
      ]
     },
     "metadata": {},
     "execution_count": 118
    }
   ],
   "source": [
    "grouped.agg({'tip_pct' : ['min', 'max', 'mean', 'std'],\n",
    "             'size' : 'sum'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Returning aggregated data in \"unindexed\" form"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      sex smoker  total_bill       tip      size   tip_pct\n",
       "0  Female     No   18.105185  2.773519  2.592593  0.156921\n",
       "1  Female    Yes   17.977879  2.931515  2.242424  0.182150\n",
       "2    Male     No   19.791237  3.113402  2.711340  0.160669\n",
       "3    Male    Yes   22.284500  3.051167  2.500000  0.152771"
      ]
     },
     "metadata": {},
     "execution_count": 119
    }
   ],
   "source": [
    "tips.groupby(['sex', 'smoker'], as_index=False).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Group-wise operations and transformations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  key1 key2     data1     data2\n",
       "0    a  one -0.204708  1.393406\n",
       "1    a  two  0.478943  0.092908\n",
       "2    b  one -0.519439  0.281746\n",
       "3    b  two -0.555730  0.769023\n",
       "4    a  one  1.965781  1.246435"
      ]
     },
     "metadata": {},
     "execution_count": 120
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      mean_data1  mean_data2\n",
       "key1                        \n",
       "a       0.746672    0.910916\n",
       "b      -0.537585    0.525384"
      ]
     },
     "metadata": {},
     "execution_count": 121
    }
   ],
   "source": [
    "k1_means = df.groupby('key1').mean().add_prefix('mean_')\n",
    "k1_means"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  key1 key2     data1     data2  mean_data1  mean_data2\n",
       "0    a  one -0.204708  1.393406    0.746672    0.910916\n",
       "1    a  two  0.478943  0.092908    0.746672    0.910916\n",
       "4    a  one  1.965781  1.246435    0.746672    0.910916\n",
       "2    b  one -0.519439  0.281746   -0.537585    0.525384\n",
       "3    b  two -0.555730  0.769023   -0.537585    0.525384"
      ]
     },
     "metadata": {},
     "execution_count": 122
    }
   ],
   "source": [
    "pd.merge(df, k1_means, left_on='key1', right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "            a         b         c         d         e\n",
       "one -0.082032 -1.063687 -1.047620 -0.884358 -0.028309\n",
       "two  0.505275 -0.849512  0.075965  0.834983  0.452620"
      ]
     },
     "metadata": {},
     "execution_count": 123
    }
   ],
   "source": [
    "key = ['one', 'two', 'one', 'two', 'one']\n",
    "people.groupby(key).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               a         b         c         d         e\n",
       "Joe    -0.082032 -1.063687 -1.047620 -0.884358 -0.028309\n",
       "Steve   0.505275 -0.849512  0.075965  0.834983  0.452620\n",
       "Wes    -0.082032 -1.063687 -1.047620 -0.884358 -0.028309\n",
       "Jim     0.505275 -0.849512  0.075965  0.834983  0.452620\n",
       "Travis -0.082032 -1.063687 -1.047620 -0.884358 -0.028309"
      ]
     },
     "metadata": {},
     "execution_count": 124
    }
   ],
   "source": [
    "people.groupby(key).transform(np.mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               a         b         c         d         e\n",
       "Joe     1.089221 -0.232534  1.322612  1.113271  1.381226\n",
       "Steve   0.381154 -1.152125 -0.447807  0.834043 -0.891190\n",
       "Wes    -0.457709       NaN       NaN -0.136869 -0.548778\n",
       "Jim    -0.381154  1.152125  0.447807 -0.834043  0.891190\n",
       "Travis -0.631512  0.232534 -1.322612 -0.976402 -0.832448"
      ]
     },
     "metadata": {},
     "execution_count": 125
    }
   ],
   "source": [
    "def demean(arr):\n",
    "    return arr - arr.mean()\n",
    "demeaned = people.groupby(key).transform(demean)\n",
    "demeaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                a             b    c             d    e\n",
       "one  0.000000e+00 -1.110223e-16  0.0  7.401487e-17  0.0\n",
       "two -2.775558e-17  0.000000e+00  0.0  0.000000e+00  0.0"
      ]
     },
     "metadata": {},
     "execution_count": 126
    }
   ],
   "source": [
    "demeaned.groupby(key).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Apply: General split-apply-combine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "ipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     total_bill   tip     sex smoker  day    time  size   tip_pct\n",
       "109       14.31  4.00  Female    Yes  Sat  Dinner     2  0.279525\n",
       "183       23.17  6.50    Male    Yes  Sun  Dinner     4  0.280535\n",
       "232       11.61  3.39    Male     No  Sat  Dinner     2  0.291990\n",
       "67         3.07  1.00  Female    Yes  Sat  Dinner     1  0.325733\n",
       "178        9.60  4.00  Female    Yes  Sun  Dinner     2  0.416667\n",
       "172        7.25  5.15    Male    Yes  Sun  Dinner     2  0.710345"
      ]
     },
     "metadata": {},
     "execution_count": 127
    }
   ],
   "source": [
    "def top(df, n=5, column='tip_pct'):\n",
    "    return df.sort_index(by=column)[-n:]\n",
    "top(tips, n=6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "ipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "            total_bill   tip     sex smoker   day    time  size   tip_pct\n",
       "smoker                                                                   \n",
       "No     88        24.71  5.85    Male     No  Thur   Lunch     2  0.236746\n",
       "       185       20.69  5.00    Male     No   Sun  Dinner     5  0.241663\n",
       "       51        10.29  2.60  Female     No   Sun  Dinner     2  0.252672\n",
       "       149        7.51  2.00    Male     No  Thur   Lunch     2  0.266312\n",
       "       232       11.61  3.39    Male     No   Sat  Dinner     2  0.291990\n",
       "Yes    109       14.31  4.00  Female    Yes   Sat  Dinner     2  0.279525\n",
       "       183       23.17  6.50    Male    Yes   Sun  Dinner     4  0.280535\n",
       "       67         3.07  1.00  Female    Yes   Sat  Dinner     1  0.325733\n",
       "       178        9.60  4.00  Female    Yes   Sun  Dinner     2  0.416667\n",
       "       172        7.25  5.15    Male    Yes   Sun  Dinner     2  0.710345"
      ]
     },
     "metadata": {},
     "execution_count": 128
    }
   ],
   "source": [
    "tips.groupby('smoker').apply(top)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "ipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                 total_bill    tip     sex smoker   day    time  size  \\\n",
       "smoker day                                                              \n",
       "No     Fri  94        22.75   3.25  Female     No   Fri  Dinner     2   \n",
       "       Sat  212       48.33   9.00    Male     No   Sat  Dinner     4   \n",
       "       Sun  156       48.17   5.00    Male     No   Sun  Dinner     6   \n",
       "       Thur 142       41.19   5.00    Male     No  Thur   Lunch     5   \n",
       "Yes    Fri  95        40.17   4.73    Male    Yes   Fri  Dinner     4   \n",
       "       Sat  170       50.81  10.00    Male    Yes   Sat  Dinner     3   \n",
       "       Sun  182       45.35   3.50    Male    Yes   Sun  Dinner     3   \n",
       "       Thur 197       43.11   5.00  Female    Yes  Thur   Lunch     4   \n",
       "\n",
       "                  tip_pct  \n",
       "smoker day                 \n",
       "No     Fri  94   0.142857  \n",
       "       Sat  212  0.186220  \n",
       "       Sun  156  0.103799  \n",
       "       Thur 142  0.121389  \n",
       "Yes    Fri  95   0.117750  \n",
       "       Sat  170  0.196812  \n",
       "       Sun  182  0.077178  \n",
       "       Thur 197  0.115982  "
      ]
     },
     "metadata": {},
     "execution_count": 129
    }
   ],
   "source": [
    "tips.groupby(['smoker', 'day']).apply(top, n=1, column='total_bill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "        count      mean       std       min       25%       50%       75%  \\\n",
       "smoker                                                                      \n",
       "No      151.0  0.159328  0.039910  0.056797  0.136906  0.155625  0.185014   \n",
       "Yes      93.0  0.163196  0.085119  0.035638  0.106771  0.153846  0.195059   \n",
       "\n",
       "             max  \n",
       "smoker            \n",
       "No      0.291990  \n",
       "Yes     0.710345  "
      ]
     },
     "metadata": {},
     "execution_count": 130
    }
   ],
   "source": [
    "result = tips.groupby('smoker')['tip_pct'].describe()\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "       smoker\n",
       "count  No        151.000000\n",
       "       Yes        93.000000\n",
       "mean   No          0.159328\n",
       "       Yes         0.163196\n",
       "std    No          0.039910\n",
       "       Yes         0.085119\n",
       "min    No          0.056797\n",
       "       Yes         0.035638\n",
       "25%    No          0.136906\n",
       "       Yes         0.106771\n",
       "50%    No          0.155625\n",
       "       Yes         0.153846\n",
       "75%    No          0.185014\n",
       "       Yes         0.195059\n",
       "max    No          0.291990\n",
       "       Yes         0.710345\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 131
    }
   ],
   "source": [
    "result.unstack('smoker')"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "f = lambda x: x.describe()\n",
    "grouped.apply(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Suppressing the group keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "ipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\nipykernel_launcher:2: FutureWarning: by argument to sort_index is deprecated, please use .sort_values(by=...)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     total_bill   tip     sex smoker   day    time  size   tip_pct\n",
       "88        24.71  5.85    Male     No  Thur   Lunch     2  0.236746\n",
       "185       20.69  5.00    Male     No   Sun  Dinner     5  0.241663\n",
       "51        10.29  2.60  Female     No   Sun  Dinner     2  0.252672\n",
       "149        7.51  2.00    Male     No  Thur   Lunch     2  0.266312\n",
       "232       11.61  3.39    Male     No   Sat  Dinner     2  0.291990\n",
       "109       14.31  4.00  Female    Yes   Sat  Dinner     2  0.279525\n",
       "183       23.17  6.50    Male    Yes   Sun  Dinner     4  0.280535\n",
       "67         3.07  1.00  Female    Yes   Sat  Dinner     1  0.325733\n",
       "178        9.60  4.00  Female    Yes   Sun  Dinner     2  0.416667\n",
       "172        7.25  5.15    Male    Yes   Sun  Dinner     2  0.710345"
      ]
     },
     "metadata": {},
     "execution_count": 132
    }
   ],
   "source": [
    "tips.groupby('smoker', group_keys=False).apply(top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Quantile and bucket analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0     (-1.23, 0.489]\n",
       "1    (-2.956, -1.23]\n",
       "2     (-1.23, 0.489]\n",
       "3     (0.489, 2.208]\n",
       "4     (-1.23, 0.489]\n",
       "5     (0.489, 2.208]\n",
       "6     (-1.23, 0.489]\n",
       "7     (-1.23, 0.489]\n",
       "8     (0.489, 2.208]\n",
       "9     (0.489, 2.208]\n",
       "Name: data1, dtype: category\n",
       "Categories (4, interval[float64]): [(-2.956, -1.23] < (-1.23, 0.489] < (0.489, 2.208] < (2.208, 3.928]]"
      ]
     },
     "metadata": {},
     "execution_count": 133
    }
   ],
   "source": [
    "frame = DataFrame({'data1': np.random.randn(1000),\n",
    "                   'data2': np.random.randn(1000)})\n",
    "factor = pd.cut(frame.data1, 4)\n",
    "factor[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                 count       max      mean       min\n",
       "data1                                               \n",
       "(-2.956, -1.23]   95.0  1.670835 -0.039521 -3.399312\n",
       "(-1.23, 0.489]   598.0  3.260383 -0.002051 -2.989741\n",
       "(0.489, 2.208]   297.0  2.954439  0.081822 -3.745356\n",
       "(2.208, 3.928]    10.0  1.765640  0.024750 -1.929776"
      ]
     },
     "metadata": {},
     "execution_count": 134
    }
   ],
   "source": [
    "def get_stats(group):\n",
    "    return {'min': group.min(), 'max': group.max(),\n",
    "            'count': group.count(), 'mean': group.mean()}\n",
    "\n",
    "grouped = frame.data2.groupby(factor)\n",
    "grouped.apply(get_stats).unstack()\n",
    "\n",
    "#ADAPT the output is not sorted in the book while this is the case now (swap first two lines)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "data1                 \n",
       "(-2.956, -1.23]  count     95.000000\n",
       "                 max        1.670835\n",
       "                 mean      -0.039521\n",
       "                 min       -3.399312\n",
       "(-1.23, 0.489]   count    598.000000\n",
       "                 max        3.260383\n",
       "                 mean      -0.002051\n",
       "                 min       -2.989741\n",
       "(0.489, 2.208]   count    297.000000\n",
       "                 max        2.954439\n",
       "                 mean       0.081822\n",
       "                 min       -3.745356\n",
       "(2.208, 3.928]   count     10.000000\n",
       "                 max        1.765640\n",
       "                 mean       0.024750\n",
       "                 min       -1.929776\n",
       "Name: data2, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 135
    }
   ],
   "source": [
    "grouped.apply(get_stats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "       count       max      mean       min\n",
       "data1                                     \n",
       "0      100.0  1.670835 -0.049902 -3.399312\n",
       "1      100.0  2.628441  0.030989 -1.950098\n",
       "2      100.0  2.527939 -0.067179 -2.925113\n",
       "3      100.0  3.260383  0.065713 -2.315555\n",
       "4      100.0  2.074345 -0.111653 -2.047939\n",
       "5      100.0  2.184810  0.052130 -2.989741\n",
       "6      100.0  2.458842 -0.021489 -2.223506\n",
       "7      100.0  2.954439 -0.026459 -3.056990\n",
       "8      100.0  2.735527  0.103406 -3.745356\n",
       "9      100.0  2.377020  0.220122 -2.064111"
      ]
     },
     "metadata": {},
     "execution_count": 136
    }
   ],
   "source": [
    "# Return quantile numbers\n",
    "grouping = pd.qcut(frame.data1, 10, labels=False)\n",
    "\n",
    "grouped = frame.data2.groupby(grouping)\n",
    "grouped.apply(get_stats).unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: Filling missing values with group-specific values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0         NaN\n",
       "1   -0.125921\n",
       "2         NaN\n",
       "3   -0.884475\n",
       "4         NaN\n",
       "5    0.227290\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 137
    }
   ],
   "source": [
    "s = Series(np.random.randn(6))\n",
    "s[::2] = np.nan\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0   -0.261035\n",
       "1   -0.125921\n",
       "2   -0.261035\n",
       "3   -0.884475\n",
       "4   -0.261035\n",
       "5    0.227290\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 138
    }
   ],
   "source": [
    "s.fillna(s.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Ohio          0.922264\n",
       "New York     -2.153545\n",
       "Vermont            NaN\n",
       "Florida      -0.375842\n",
       "Oregon        0.329939\n",
       "Nevada             NaN\n",
       "California    1.105913\n",
       "Idaho              NaN\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 139
    }
   ],
   "source": [
    "states = ['Ohio', 'New York', 'Vermont', 'Florida',\n",
    "          'Oregon', 'Nevada', 'California', 'Idaho']\n",
    "group_key = ['East'] * 4 + ['West'] * 4\n",
    "data = Series(np.random.randn(8), index=states)\n",
    "data[['Vermont', 'Nevada', 'Idaho']] = np.nan\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "East   -0.535707\n",
       "West    0.717926\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 140
    }
   ],
   "source": [
    "data.groupby(group_key).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Ohio          0.922264\n",
       "New York     -2.153545\n",
       "Vermont      -0.535707\n",
       "Florida      -0.375842\n",
       "Oregon        0.329939\n",
       "Nevada        0.717926\n",
       "California    1.105913\n",
       "Idaho         0.717926\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 141
    }
   ],
   "source": [
    "fill_mean = lambda g: g.fillna(g.mean())\n",
    "data.groupby(group_key).apply(fill_mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Ohio          0.922264\n",
       "New York     -2.153545\n",
       "Vermont       0.500000\n",
       "Florida      -0.375842\n",
       "Oregon        0.329939\n",
       "Nevada       -1.000000\n",
       "California    1.105913\n",
       "Idaho        -1.000000\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 143
    }
   ],
   "source": [
    "fill_values = {'East': 0.5, 'West': -1}\n",
    "fill_func = lambda g: g.fillna(fill_values[g.name])\n",
    "\n",
    "data.groupby(group_key).apply(fill_func)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: Random sampling and permutation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Hearts, Spades, Clubs, Diamonds\n",
    "suits = ['H', 'S', 'C', 'D']\n",
    "card_val = (list(range(1, 11)) + [10] * 3) * 4\n",
    "base_names = ['A'] + list(range(2, 11))+ ['J', 'K', 'Q']\n",
    "cards = []\n",
    "for suit in ['H', 'S', 'C', 'D']:\n",
    "    cards.extend(str(num) + suit for num in base_names)\n",
    "\n",
    "deck = Series(card_val, index=cards)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "AH      1\n",
       "2H      2\n",
       "3H      3\n",
       "4H      4\n",
       "5H      5\n",
       "6H      6\n",
       "7H      7\n",
       "8H      8\n",
       "9H      9\n",
       "10H    10\n",
       "JH     10\n",
       "KH     10\n",
       "QH     10\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "deck[:13]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "AC     1\n",
       "AD     1\n",
       "QC    10\n",
       "QS    10\n",
       "7H     7\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 14
    }
   ],
   "source": [
    "def draw(deck, n=5):\n",
    "    return deck.take(np.random.permutation(len(deck))[:n])\n",
    "draw(deck)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "3S    3\n",
       "6C    6\n",
       "4S    4\n",
       "7S    7\n",
       "8S    8\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "source": [
    "def draw2(deck, n=5):\n",
    "    return deck.sample(n)\n",
    "draw2(deck)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_suit = lambda card: card[-1] # last letter is suit\n",
    "deck.groupby(get_suit).apply(draw, n=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# alternatively\n",
    "deck.groupby(get_suit, group_keys=False).apply(draw, n=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: Group weighted average and correlation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = DataFrame({'category': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'],\n",
    "                'data': np.random.randn(8),\n",
    "                'weights': np.random.rand(8)})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped = df.groupby('category')\n",
    "get_wavg = lambda g: np.average(g['data'], weights=g['weights'])\n",
    "grouped.apply(get_wavg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "close_px = pd.read_csv('ch09/stock_px.csv', parse_dates=True, index_col=0)\n",
    "close_px.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "close_px[-4:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rets = close_px.pct_change().dropna()\n",
    "spx_corr = lambda x: x.corrwith(x['SPX'])\n",
    "by_year = rets.groupby(lambda x: x.year)\n",
    "by_year.apply(spx_corr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Annual correlation of Apple with Microsoft\n",
    "by_year.apply(lambda g: g['AAPL'].corr(g['MSFT']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: Group-wise linear regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import statsmodels.api as sm\n",
    "def regress(data, yvar, xvars):\n",
    "    Y = data[yvar]\n",
    "    X = data[xvars]\n",
    "    X['intercept'] = 1.\n",
    "    result = sm.OLS(Y, X).fit()\n",
    "    return result.params"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "by_year.apply(regress, 'AAPL', ['SPX'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot tables and Cross-tabulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips.pivot_table(index=['sex', 'smoker'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips.pivot_table(['tip_pct', 'size'], index=['sex', 'day'],\n",
    "                 columns='smoker')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips.pivot_table(['tip_pct', 'size'], index=['sex', 'day'],\n",
    "                 columns='smoker', margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips.pivot_table('tip_pct', index=['sex', 'smoker'], columns='day',\n",
    "                 aggfunc=len, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips.pivot_table('size', index=['time', 'sex', 'smoker'],\n",
    "                 columns='day', aggfunc='sum', fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cross-tabulations: crosstab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from StringIO import StringIO\n",
    "data = \"\"\"\\\n",
    "Sample    Gender    Handedness\n",
    "1    Female    Right-handed\n",
    "2    Male    Left-handed\n",
    "3    Female    Right-handed\n",
    "4    Male    Right-handed\n",
    "5    Male    Left-handed\n",
    "6    Male    Right-handed\n",
    "7    Female    Right-handed\n",
    "8    Female    Left-handed\n",
    "9    Male    Right-handed\n",
    "10    Female    Right-handed\"\"\"\n",
    "data = pd.read_table(StringIO(data), sep='\\s+')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.crosstab(data.Gender, data.Handedness, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.crosstab([tips.time, tips.day], tips.smoker, margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example: 2012 Federal Election Commission Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fec = pd.read_csv('ch09/P00000001-ALL.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fec.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fec.ix[123456]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_cands = fec.cand_nm.unique()\n",
    "unique_cands"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_cands[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "parties = {'Bachmann, Michelle': 'Republican',\n",
    "           'Cain, Herman': 'Republican',\n",
    "           'Gingrich, Newt': 'Republican',\n",
    "           'Huntsman, Jon': 'Republican',\n",
    "           'Johnson, Gary Earl': 'Republican',\n",
    "           'McCotter, Thaddeus G': 'Republican',\n",
    "           'Obama, Barack': 'Democrat',\n",
    "           'Paul, Ron': 'Republican',\n",
    "           'Pawlenty, Timothy': 'Republican',\n",
    "           'Perry, Rick': 'Republican',\n",
    "           \"Roemer, Charles E. 'Buddy' III\": 'Republican',\n",
    "           'Romney, Mitt': 'Republican',\n",
    "           'Santorum, Rick': 'Republican'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fec.cand_nm[123456:123461]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fec.cand_nm[123456:123461].map(parties)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add it as a column\n",
    "fec['party'] = fec.cand_nm.map(parties)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fec['party'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(fec.contb_receipt_amt > 0).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fec = fec[fec.contb_receipt_amt > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fec_mrbo = fec[fec.cand_nm.isin(['Obama, Barack', 'Romney, Mitt'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Donation statistics by occupation and employer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fec.contbr_occupation.value_counts()[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "occ_mapping = {\n",
    "   'INFORMATION REQUESTED PER BEST EFFORTS' : 'NOT PROVIDED',\n",
    "   'INFORMATION REQUESTED' : 'NOT PROVIDED',\n",
    "   'INFORMATION REQUESTED (BEST EFFORTS)' : 'NOT PROVIDED',\n",
    "   'C.E.O.': 'CEO'\n",
    "}\n",
    "\n",
    "# If no mapping provided, return x\n",
    "f = lambda x: occ_mapping.get(x, x)\n",
    "fec.contbr_occupation = fec.contbr_occupation.map(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "emp_mapping = {\n",
    "   'INFORMATION REQUESTED PER BEST EFFORTS' : 'NOT PROVIDED',\n",
    "   'INFORMATION REQUESTED' : 'NOT PROVIDED',\n",
    "   'SELF' : 'SELF-EMPLOYED',\n",
    "   'SELF EMPLOYED' : 'SELF-EMPLOYED',\n",
    "}\n",
    "\n",
    "# If no mapping provided, return x\n",
    "f = lambda x: emp_mapping.get(x, x)\n",
    "fec.contbr_employer = fec.contbr_employer.map(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "by_occupation = fec.pivot_table('contb_receipt_amt',\n",
    "                                index='contbr_occupation',\n",
    "                                columns='party', aggfunc='sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "over_2mm = by_occupation[by_occupation.sum(1) > 2000000]\n",
    "over_2mm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "over_2mm.plot(kind='barh')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_top_amounts(group, key, n=5):\n",
    "    totals = group.groupby(key)['contb_receipt_amt'].sum()\n",
    "\n",
    "    # Order totals by key in descending order\n",
    "    return totals.order(ascending=False)[-n:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped = fec_mrbo.groupby('cand_nm')\n",
    "grouped.apply(get_top_amounts, 'contbr_occupation', n=7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped.apply(get_top_amounts, 'contbr_employer', n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Bucketing donation amounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bins = np.array([0, 1, 10, 100, 1000, 10000, 100000, 1000000, 10000000])\n",
    "labels = pd.cut(fec_mrbo.contb_receipt_amt, bins)\n",
    "labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped = fec_mrbo.groupby(['cand_nm', labels])\n",
    "grouped.size().unstack(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bucket_sums = grouped.contb_receipt_amt.sum().unstack(0)\n",
    "bucket_sums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "normed_sums = bucket_sums.div(bucket_sums.sum(axis=1), axis=0)\n",
    "normed_sums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "normed_sums[:-2].plot(kind='barh', stacked=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Donation statistics by state"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped = fec_mrbo.groupby(['cand_nm', 'contbr_st'])\n",
    "totals = grouped.contb_receipt_amt.sum().unstack(0).fillna(0)\n",
    "totals = totals[totals.sum(1) > 100000]\n",
    "totals[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "percent = totals.div(totals.sum(1), axis=0)\n",
    "percent[:10]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python373jvsc74a57bd0af09bc94d41e018aa4bb791c06386d7d2a0d085b02fa573368449120a8cb3c6e",
   "display_name": "Python 3.7.3 64-bit ('base': conda)"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}